﻿using System.Collections.Generic;
using System.Text;
using System.Threading.Tasks;
using ESoft.DataAccess.Models;
using ESoft.Extensions;

namespace ESoft.DataAccess.EfCore.SqlServer
{
    public class SqlServerQueryRepositry : BaseSqlQueryRepositry, ISqlRepositry
    {
        public SqlServerQueryRepositry(DataContext db) : base(db)
        {
        }

        public override PageResult<T> FindPageBySql<T>(string rawSql, PageInput pageInput, string orderBy, params object[] parameters)
        {
            var pageSql = GetPageSql(rawSql, pageInput, orderBy);

            var items = FindBySql<T>(pageSql, parameters);

            var countSql = $"SELECT count(*) FROM ({rawSql}) as T";
            var count = ExecuteScalar<int>(countSql, parameters);

            return new PageResult<T>(items, count);
        }

        public override async Task<PageResult<T>> FindPageBySqlAsync<T>(string rawSql, PageInput pageInput, string orderBy, params object[] parameters)
        {
            var pageSql = GetPageSql(rawSql, pageInput, orderBy);

            var items = await FindBySqlAsync<T>(pageSql, parameters);

            var countSql = $"SELECT count(*) FROM ({rawSql}) as T";
            var count = await ExecuteScalarAsync<int>(countSql, parameters);

            return new PageResult<T>(items, count);
        }

        private static string GetPageSql(string rawSql, PageInput pageInput, string orderBy)
        {
            return $"SELECT  *  FROM ({rawSql})" +
                             $"(SELECT *,ROW_NUMBER() OVER({orderBy}) ROW_ID" +
                             $" FROM ({rawSql}) AS T) AS T2 " +
                             "WHERE T2.ROW_ID " +
                             $"BETWEEN {(pageInput.Page - 1) * pageInput.Limit + 1} AND {pageInput.Page * pageInput.Limit}";
        }

        public string GetPageSql(string columns, string tableName, string orderBy, PageInput pageInput,string whereStr)
        {
            StringBuilder sbSql=new StringBuilder($"SELECT ROW_NUMBER() OVER({orderBy}) ROW_ID,{columns} FROM {tableName}");
            if (!whereStr.IsNullOrWhiteSpace())
            {
                sbSql.Append("WHERE");
                sbSql.Append(whereStr);
            }
            
            
            return $"SELECT * FROM ({sbSql})AS T WHERE T.ROW_ID BETWEEN {(pageInput.Page - 1) * pageInput.Limit + 1} AND {pageInput.Page * pageInput.Limit}";
        }
    }
}